/**
	Name                :  Stephen Durfey
	Course Number: CS462
	Semester         :  Spring 2009
	Assignment      : 7
	Due Date          : Friday, April 3rd, 12:00pm
	
	Description: This program is a simple database application that will perform specified select statements on a set of three relations
			DEPARTMENT, INTERSECTION, and FACULTY. The user will be able to select what type of query
			they would like to perform, they have two options: a query on the department, which will return a list of all faculty members in
			that department (if any), and a query on a particular faculty member, which will return all departments associated with that 
            faculty member. If no results are found, the user is
            notified. modified*****
*/

#include <iostream>
#include <string>
#include <iomanip>
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <unistd.h>

using namespace std;

//Declare Section

EXEC SQL BEGIN DECLARE SECTION;

	VARCHAR   username[20];
	VARCHAR   password[20];
	VARCHAR   sqlStatement[800];

	int       DS_DeptNum;
	VARCHAR   DS_DeptName[30];
	VARCHAR   DS_DeptLocation[30];
	VARCHAR   DS_DeptPhone[12];

	int       DS_FacNum;
	int       DS_FacYearStart;
	VARCHAR   DS_FacName[20];
	VARCHAR   DS_FacRank[4];

	int       DS_IDeptNum;
	int       DS_IFacNum;
	VARCHAR   DS_IOffice[30];
	VARCHAR   DS_IPhone[12];

EXEC SQL END DECLARE SECTION;

//include SQL communications area

EXEC SQL INCLUDE SQLCA;

//function declarations

void connect();  //connects to Oracle
void buildSqlSelectDepartment( string department ); //builds specified sql statement for a department query
void buildSqlSelectFaculty( string facultyNum );    //builds specified sql statement for a faculty member query
void prepareCursor(); //prepares cursor
void executeSQL( string queryType ); //executes sql statement
void printResultsDepartment( int counter ); //prints out results from select statement on a department
void printResultsFaculty( int counter ); //prints out results from select statement on a faculty member
void commit(); //severs Oracle connection
void sqlErrorHandle(char *); //handles errors in SQL code
void notFound( ); //called whenver the select statement doesnt return any tuples
int executeDeleteSQL();
void deleteEmptyDepartment( string department );

int counter = 0;

int main()
{
	int decision = 0;
	string department,
		   facultyNum;
	
	//defines which function to call in case of an SQL error
	EXEC SQL WHENEVER SQLERROR do sqlErrorHandle("***Oracle Error:  ");
	
	//connects to DB
	connect();
	
	cout << "Welcome to the interoffice database search application!\n";
	cout << "Specify a search type below, please\n\n";
	
	//displays menu for user to select options from, continues to loop until the user presses '3'
	//to exit the proram, which will call the commit function
	do
	{
		cout  << "(1) Department Query     (lists all faculty members within specified department number)\n";
		cout  << "(2) Faculty Member Query (lists all departments the specified employee number is associated with\n";
		cout  << "(3) Delete Empty Department\n";
		cout  << "(4) Exit\n";
		cout  << "Enter choice: ";
		cin   >> decision;

		
		if( decision == 1 )
		{
			cout << "\n\nEnter Department Number: ";
			cin  >> department;
			buildSqlSelectDepartment( department );
			prepareCursor();
			executeSQL( "department" );
		}
		else if( decision == 2 )
		{
			cout << "\n\nEnter Faculty Number: ";
			cin  >> facultyNum;
			buildSqlSelectFaculty( facultyNum );
			prepareCursor();
			executeSQL( "faculty" );
		}
		else if( decision == 3 )
		{
			cout << "\n\nEnter Department Number: ";
			cin  >> department;
			deleteEmptyDepartment(department);

		}
		
		if( decision != 1 && decision != 2 && decision != 3 )
		{
			cout << "I am sorry, I cannot process your request, please select the correct option from the menu, thank you\n\n\n";
		}
		
	}
	while( decision != 3 );
	
	commit();

	return 0;
}//end of main function

//asks for username and password, and attempts to connect to Oracle
void connect()
{
	//gathers the Oracle username and password
	char* temp_password;

	cout << "Enter Oracle Username: ";
	cin >> username.arr;

	temp_password = getpass("Enter Oracle Password: ");
	strcpy((char*)password.arr, temp_password);

	//gets length of username and password
	username.len = strlen( (char*) username.arr );
	password.len = strlen( (char*) password.arr );

	//connects to Oracle, with the specified username and password
	EXEC SQL CONNECT :username IDENTIFIED BY :password;

	cout << "Connected to Oracle as user: " << (char*) username.arr << endl << endl;

}//end of connect function


//builds the SQL select query, each time the function is called, the user is prompted to enter a  department number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectDepartment( string department )
{

	strcpy( (char*) sqlStatement.arr,  "SELECT   INTERSECTION.IDeptNum, DEPARTMENT.DeptName,\n"
									   "         DEPARTMENT.DeptLocation, INTERSECTION.IFacNum,\n"
									   "         FACULTY.FacName, INTERSECTION.IOffice,\n"
									   "         INTERSECTION.IPhone, FACULTY.FacRank, FACULTY.FacYearStart\n"
									   "FROM     DEPARTMENT\n"
									   "JOIN     INTERSECTION\n"
									   "ON       DEPARTMENT.DeptNum = INTERSECTION.IDeptNum\n"
									   "JOIN     FACULTY\n"
									   "ON       INTERSECTION.IFacNum = FACULTY.FacNum\n"
									   "WHERE    DEPARTMENT.DeptNum = ");

	strcat( (char*) sqlStatement.arr, department.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl; 
}//end of buildSqlSelectDepartment function

//builds the SQL select query, each time the function is called, the user is prompted to enter a faculty member number, which is then
//catatenated onto the end of the SELECT statement, and subsequently printed to the screen for your viewing pleasure.
void buildSqlSelectFaculty( string facultyNum )
{
	strcpy( (char*) sqlStatement.arr,  "SELECT   INTERSECTION.IDeptNum, DEPARTMENT.DeptName,\n"
									   "         DEPARTMENT.DeptLocation, INTERSECTION.IFacNum,\n"
									   "         FACULTY.FacName, INTERSECTION.IOffice,\n"
									   "         INTERSECTION.IPhone, FACULTY.FacRank, FACULTY.FacYearStart\n"
									   "FROM     FACULTY\n"
									   "JOIN     INTERSECTION\n"
									   "ON       FACULTY.FacNum = INTERSECTION.IFacNum\n"
									   "JOIN     DEPARTMENT\n"
									   "ON       INTERSECTION.IDeptNum = DEPARTMENT.DeptNum\n"
									   "WHERE    FACULTY.FacNum = ");

	strcat( (char*) sqlStatement.arr, facultyNum.c_str() );
								
	cout << "Query is:  \n" << sqlStatement.arr << endl;
} //end of buildSqlSelectFaculty function

void prepareCursor()
{
	sqlStatement.len = strlen( (char*) sqlStatement.arr );

	//prepares SQL select statement
	cout << "fails, line 207\n\n";
	EXEC SQL PREPARE S1 FROM :sqlStatement;
	

	//associate cursor with a prepared sql statement
	
	EXEC SQL DECLARE C1 CURSOR FOR S1;

	//open the cursor, execute SQL statement, place tuples in the cursor
	EXEC SQL OPEN C1;
}//end of prepareCursor function

void executeSQL( string queryType )
{
	counter = 0; //keeps track on number of iterations, used in three functions, notFound, printResultsDepartment, and printResultsFaculty

	//loops through the cursor, and extracts the data, and stores that data into each variable in the
	//declare section, as specified 
	do
	{
		//if zero tuples were selected from DB, call the function notFound
		EXEC SQL WHENEVER NOT FOUND do notFound( );
		EXEC SQL FETCH C1 INTO  DS_IDeptNum,
								DS_DeptName,
								DS_DeptLocation,
								DS_IFacNum,
								DS_FacName,
								DS_IOffice,
								DS_IPhone,
								DS_FacRank,
								DS_FacYearStart;

		if( sqlca.sqlcode == 0 )
		{
			if( queryType == "department" )
				printResultsDepartment( counter );
			else
				printResultsFaculty( counter );
		}
			
		counter++;
		
		
	}
	while( sqlca.sqlcode == 0 ); //end of retrieve do-while loop
	
	cout << endl << endl;
	counter = 0;
	
}//end of executeSQL function

void deleteEmptyDepartment( string department )
{
	int numTuples;
//	char sql[100] = "delete from department where deptname = 03";

	strcpy( (char*) sqlStatement.arr,  "SELECT FacName, DEPARTMENT.DeptName, DEPARTMENT.DeptNum, DEPARTMENT.DeptLocation\n "
									   "FROM   FACULTY\n"
									   "JOIN   INTERSECTION\n"
									   "ON     FACULTY.FacNum = INTERSECTION.IFacNum\n"
									   "JOIN   DEPARTMENT\n"
									   "ON     INTERSECTION.IDeptNum = DEPARTMENT.DeptNum\n"
									   "WHERE  DeptNum =");

	strcat( (char*) sqlStatement.arr, department.c_str() );
	cout << "here\n\n\n"; 
	prepareCursor();
	cout << "no, here\n\n\n\n";
	//EXEC SQL PREPARE sqlCom FROM :sqlStatement;
	numTuples = executeDeleteSQL();
	cout << "no, really, here\n\n";

	if( numTuples = 0 )
	{
		strcpy( (char*) sqlStatement.arr, "delete from department where deptname = 03");
		//prepareCursor();
		//EXEC SQL PREPARE sqlCom FROM :sqlStatement;
		
		//EXEC SQL DELETE FROM DEPARTMENT WHERE DEPTNAME = department;
		EXEC SQL EXECUTE S1;
	
		cout << "Department:  " << DS_DeptName.arr << " was successfully deleted\n\n";

		
	}
	else if( numTuples == 2 )
	{
		cout << "department not empty\n\n\n";
	}
	else if( numTuples > 2 )
	{
		//todo: inform user that department cannot be deleted, and why
	}

}

int executeDeleteSQL()
{
	do
	{
		//if zero tuples were selected from DB, call the function notFound
		
		EXEC SQL FETCH C1 INTO  DS_FacName,
                                DS_DeptNum,
								DS_DeptName,
                                DS_DeptLocation;
	}
	while( sqlca.sqlcode == 0 ); //end of retrieve do-while loop

	//returns # of tuples fetched from the relation
	return sqlca.sqlerrd[2];
}

//called and printed if no rows were selected from the database
//if counter is equal to 0, then it means this function was called during the 
//first iteration of the do-while loop in the previous function, prepareExecute()
void notFound( )
{

	if( counter == 0 )
		cout << "\n\nNo tuples found matching the requested statement\n\n";
}//end of notFound function

//prints the results of each department, by listing all faculty memebers of that department
 void printResultsDepartment( int counter )
 {
	 
	//prints header information for department being queried
	if( counter == 0 )
	{
		cout << "\n\n\n---------------------------------- Department -----------------------------------\n";
		
		cout << "               Depart Num :  " <<  DS_IDeptNum                 << endl;
		cout << "               Depart Name:  " << (char*) DS_DeptName.arr      << endl;
		cout << "               Location   :  " << (char*) DS_DeptLocation.arr  << endl;       
		
		cout << "----------------------------------- Faculty Member(s) ---------------------------------\n\n";
		cout << "    Name             Num         Office            Phone         Rank      Start Year\n";
	    cout << "============        =====   =================  =============    ========  ===========\n\n";
	}//end of print header if statement
	
	//prints specified variables in a uniform and formatted way
	cout << setw(20) << left << (char *) DS_FacName.arr;
	cout << setw(8)  << left << DS_IFacNum;
	cout << setw(19) << left << (char *) DS_IOffice.arr;
	cout << setw(17) << left << (char *) DS_IPhone.arr; 
	cout << setw(10) << left << (char *) DS_FacRank.arr;
	cout << setw(10) << left << DS_FacYearStart << endl;
	
	
	strcpy( (char *) DS_DeptName.arr,     "                 " );
	strcpy( (char *) DS_DeptLocation.arr, "                 " );
	strcpy( (char *) DS_IOffice.arr,      "                 " );
	strcpy( (char *) DS_FacName.arr,      "                 " );
	strcpy( (char *) DS_IPhone.arr,       "                 " );
	strcpy( (char *) DS_FacRank.arr,      "                 " );

 } //end print deparment function
 
 //prints results of each faculty memeber specified, by printing out the department information
 //for each department the faculty member is a member of.
 void printResultsFaculty( int counter )
 {
	//prints header only once. the counter variable ensures this
	if( counter == 0 )
	{
		cout << "\n\n\n-------------------------- Faculty Member ------------------------------\n";
		
		cout << "               Faculty Num:  " <<  DS_IFacNum                  << endl;
		cout << "               Name       :  " << (char*) DS_FacName.arr       << endl;
		cout << "               Rank       :  " << (char*) DS_FacRank.arr       << endl;
		cout << "               Year Start :  " <<  DS_FacYearStart             << endl;       
		
		cout << "---------------------------- Department(s)  ----------------------------\n\n";
		cout << "  Num      Department Name           Office            Location           Phone\n";
		cout << " =====   ===================    ================    ==============     =============\n";
	}//end print header if statment
	
	//prints output from variables in the declare section
	cout << "   " << setw(8)   << left << DS_IDeptNum;
	cout << setw(22)  << left << (char*) DS_DeptName.arr;
	cout << setw(19)  << left << (char *) DS_IOffice.arr;
	cout << setw(20)  << left << (char*) DS_DeptLocation.arr; 
	cout << setw(10)  << left << (char*) DS_IPhone.arr << endl;

	strcpy( (char *) DS_DeptName.arr,     "                 " );
	strcpy( (char *) DS_DeptLocation.arr, "                 " );
	strcpy( (char *) DS_IOffice.arr,      "                 " );
	strcpy( (char *) DS_FacName.arr,      "                 " );
	strcpy( (char *) DS_IPhone.arr,       "                 " );
	strcpy( (char *) DS_FacRank.arr,      "                 " );

 } //end print faculty function
 
//commits work performed on database and severs connection
void commit()
{
	EXEC SQL COMMIT WORK RELEASE;
}//end commit function

//error handler used everytime an SQL error is generated in Oracle, could be an incorrect login name/password
//or an error in SQL syntax
void sqlErrorHandle( char * message )
{
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	
	cout << endl << message << endl;
	cout << sqlca.sqlerrm.sqlerrmc << endl;
	
	EXEC SQL ROLLBACK RELEASE;
	
	exit(1);
} //end sqlError function
